package com.pfizer.DB;

import static com.pfizer.Entity.Handout.HANDOUTCLASSSTRING;
import static com.pfizer.Entity.Handout.HANDOUTDLPICCNTSTRING;
import static com.pfizer.Entity.Handout.HANDOUTFESTRING;
import static com.pfizer.Entity.Handout.HANDOUTICONSTRING;
import static com.pfizer.Entity.Handout.HANDOUTIDSTRING;
import static com.pfizer.Entity.Handout.HANDOUTLUTIMESTRING;
import static com.pfizer.Entity.Handout.HANDOUTPICCNTSTRING;
import static com.pfizer.Entity.Handout.HANDOUTQNSTRING;
import static com.pfizer.Entity.Handout.HANDOUTSUBJSTRING;
import static com.pfizer.Entity.Handout.HANDOUTURLPATHSTRING;
import static com.pfizer.Entity.HandoutClass.HCIDSTRING;
import static com.pfizer.Entity.HandoutClass.HCNAMESTRING;
import static com.pfizer.Entity.Question.ANSWERTYPESTRING;
import static com.pfizer.Entity.Question.MAXSELECTCNTSTRING;
import static com.pfizer.Entity.Question.MINSELECTCNTSTRING;
import static com.pfizer.Entity.Question.OPTIONCNTSTRING;
import static com.pfizer.Entity.Question.OPTIONSTRING;
import static com.pfizer.Entity.Question.QUESTIONIDSTRING;
import static com.pfizer.Entity.Question.QUESTIONNAIREIDSTRING;
import static com.pfizer.Entity.Question.QUESTIONNOSTRING;
import static com.pfizer.Entity.Question.QUESTIONSUBJSTRING;
import static com.pfizer.Entity.Survey.SURVEYBGTIMESTRING;
import static com.pfizer.Entity.Survey.SURVEYCLASSSTRING;
import static com.pfizer.Entity.Survey.SURVEYEDTIMESTRING;
import static com.pfizer.Entity.Survey.SURVEYIDSTRING;
import static com.pfizer.Entity.Survey.SURVEYLUTIMESTRING;
import static com.pfizer.Entity.Survey.SURVEYQNSTRING;
import static com.pfizer.Entity.Survey.SURVEYREMARKSTRING;
import static com.pfizer.Entity.Survey.SURVEYSUBJSTRING;
import static com.pfizer.Entity.SurveyClass.SCIDSTRING;
import static com.pfizer.Entity.SurveyClass.SCNAMESTRING;
import static com.pfizer.Entity.SurveyResult.DIVISIONSSTRING;
import static com.pfizer.Entity.SurveyResult.DOCTORNAMESTRING;
import static com.pfizer.Entity.SurveyResult.HOSPITALSTRING;
import static com.pfizer.Entity.SurveyResult.SRIDSTRING;
import static com.pfizer.Entity.SurveyResult.SRQUESTIONNAIRESTRING;
import static com.pfizer.Entity.SurveyResult.SRSURVEYIDSTRING;
import static com.pfizer.Entity.SurveyResult.SURVEYRESULTCONTSTRING;
import static com.pfizer.Entity.SurveyResult.SURVEYRESULTFLAGSTRING;
import static com.pfizer.Entity.SurveyResult.SURVEYRESULTTIMESTRING;
import static com.pfizer.Entity.UserInfo.LOGINTIMESTRING;
import static com.pfizer.Entity.UserInfo.PROVECODESTRING;
import static com.pfizer.Entity.UserInfo.USEREMAILSTRING;
import static com.pfizer.Entity.UserInfo.USERIDSTRING;
import static com.pfizer.Entity.UserInfo.USERNAMESTRING;
import static com.pfizer.Entity.UserInfo.USERPSWSTRING;
import static com.pfizer.Entity.UserLog.USERLOGCASECODESTRING;
import static com.pfizer.Entity.UserLog.USERLOGCLASSSTRING;
import static com.pfizer.Entity.UserLog.USERLOGIDSTRING;
import static com.pfizer.Entity.UserLog.USERLOGPARENTIDSTRING;
import static com.pfizer.Entity.UserLog.USERLOGTIMESTRING;
import static com.pfizer.Entity.UserLog.USERLOGTYPESTRING;
import static com.pfizer.Entity.UserLog.USERLOGUSERIDSTRING;
import static com.pfizer.Entity.VideoClass.VCIDSTRING;
import static com.pfizer.Entity.VideoClass.VCNAMESTRING;
import static com.pfizer.Entity.pMessage.MSGCONTSTRING;
import static com.pfizer.Entity.pMessage.MSGFLAGSTRING;
import static com.pfizer.Entity.pMessage.MSGIDSTRING;
import static com.pfizer.Entity.pMessage.MSGRECEIVERIDSTRING;
import static com.pfizer.Entity.pNews.NEWSBGTIMESTRING;
import static com.pfizer.Entity.pNews.NEWSCONTSTRING;
import static com.pfizer.Entity.pNews.NEWSEDTIMESTRING;
import static com.pfizer.Entity.pNews.NEWSIDSTRING;
import static com.pfizer.Entity.pNews.NEWSSUBJSTRING;
import static com.pfizer.Entity.pNews.PUBLISHERSTRING;
import static com.pfizer.Entity.pVideo.VIDEOCLASSSTRING;
import static com.pfizer.Entity.pVideo.VIDEODESCSTRING;
import static com.pfizer.Entity.pVideo.VIDEOIDSTRING;
import static com.pfizer.Entity.pVideo.VIDEOLOCALFILESTRING;
import static com.pfizer.Entity.pVideo.VIDEOLOCALPICSTRING;
import static com.pfizer.Entity.pVideo.VIDEOLUTIMESTRING;
import static com.pfizer.Entity.pVideo.VIDEONAMESTRING;
import static com.pfizer.Entity.pVideo.VIDEONETFILESTRING;
import static com.pfizer.Entity.pVideo.VIDEOPICSTRING;
import static com.pfizer.Entity.pVideo.VIDEOQNSTRING;
import static com.pfizer.Entity.pVideo.VIDEOSOURCESTRING;
import static com.pfizer.Entity.pVideo.VIDEOTYPESTRING;
import static com.pfizer.Entity.pVideo.VIDEOUPDTIMESTRING;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

/**
 * DBUtils--<b>數據庫助手類</b>--
 * 該類包括了數據庫各表的創建
 * 
 * @author krui
 * @version 1.40 2011/11/19
 */
public class DBUtils extends SQLiteOpenHelper{
	//定義需要創建的各個表名
	public static final String DBNAME = "pfizerDB.db";	
	public static final String TABLEUSERS = "tblUsers";
	public static final String TABLEVIDEO = "tblVideo";
	public static final String TABLEVIDEOCLASS = "tblVideoClass";
	public static final String TABLEMESSAGE = "tblMessage";
	public static final String TABLEHANDOUT = "tblHandout";
	public static final String TABLEHANDOUTCLASS = "tblHandoutClass";
	public static final String TABLESURVEY = "tblSurvey";
	public static final String TABLESURVEYCLASS = "tblSurveyClass";
	public static final String TABLEQUESTION = "tblQuestion";
	public static final String TABLENEWS = "tblNews";
	public static final String TABLEUSERLOG = "tblUserLog";
	public static final String TABLEFILEDOWNLOG="tblFileDownlog";
	public static final String TABLESURVEYRESULT = "tblSurveyResult";
	public static final String TABLEPUNCHCARDINFO = "tblPunchCardInfo";
	public static final String Config_TABLENAME = "tblpfizerConfig";		
	public static final String cID="_id";					
	public static final String cConfigName="ConfigName";					
	public static final String cConfigValue="ConfigValue";					
	
	public DBUtils(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}
	
	/**
	 * onCreat:創建數據庫時調用
	 * 此方法中建立各個表
	 * 
	 */

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL("CREATE TABLE IF NOT EXISTS tblCount (id integer,lpCount INTEGER,lpDateTime INTEGER)");
		db.execSQL("Insert into tblCount(id,lpCount,lpDateTime) Values (1,0,0)");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS tblFileDownlog (id integer primary key autoincrement, downpath varchar(100), threadid INTEGER, downlength INTEGER,downType INTEGER,parentID INTEGER,localFile varchar(100))");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS tblFileDowned (id integer primary key autoincrement, downpath varchar(100),localFile varchar(100))");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS tblFileDowning (id integer primary key autoincrement,downType INTEGER,parentID INTEGER, downpath varchar(100),localFile varchar(100))");
		//登陸用戶表		
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLEUSERS 
				+ "("
				+ USERIDSTRING + " vachar(50),"
				+ USERNAMESTRING + " vachar(50),"
				+ USERPSWSTRING + " vachar(100),"
				+ USEREMAILSTRING + " vachar(100),"
				+ LOGINTIMESTRING + " vachar(50),"
				+ PROVECODESTRING + " vachar(50)"
				+ ")"
				);
		//影片分類
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLEVIDEOCLASS
				+ "("
				+ VCIDSTRING + " integer,"
				+ VCNAMESTRING + " vachar(50)"
				+ ")"
				);
		//影片
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLEVIDEO 
				+ "("
				+ VIDEOIDSTRING + " integer," 
				+ VIDEONAMESTRING + " vachar(100),"
				+ VIDEOTYPESTRING + " vachar(1),"
				+ VIDEODESCSTRING + " vachar(1000),"
				+ VIDEOSOURCESTRING + " vachar(500),"
				+ VIDEONETFILESTRING + " vachar(100),"
				+ VIDEOPICSTRING + " vachar(100),"
				+ VIDEOUPDTIMESTRING + " integer,"
				+ VIDEOCLASSSTRING + " vachar(20),"
				+ VIDEOQNSTRING + " integer,"
				+ VIDEOLUTIMESTRING + " integer,"
				+ VIDEOLOCALPICSTRING + " vachar(100),"
				+ VIDEOLOCALFILESTRING + " vachar(100)"
				+ ")"
				);
		//文章分類
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLEHANDOUTCLASS
				+ "("
				+ HCIDSTRING + " integer,"
				+ HCNAMESTRING + " vachar(50)"
				+ ")"
				);
		//對文章分類初始化數據
		db.execSQL("Insert into "+TABLEHANDOUTCLASS+"("+HCIDSTRING+","+HCNAMESTRING+") Values (1,'RA 類風濕性關節炎')");
		db.execSQL("Insert into "+TABLEHANDOUTCLASS+"("+HCIDSTRING+","+HCNAMESTRING+") Values (2,'AS 僵直性脊髓炎')");
		db.execSQL("Insert into "+TABLEHANDOUTCLASS+"("+HCIDSTRING+","+HCNAMESTRING+") Values (3,'PsO 乾癬')");
		db.execSQL("Insert into "+TABLEHANDOUTCLASS+"("+HCIDSTRING+","+HCNAMESTRING+") Values (4,'PsA 乾癬性關節炎')");
		
		//文章
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLEHANDOUT 
				+ "("
				+ HANDOUTIDSTRING + " integer," 
				+ HANDOUTICONSTRING + " vachar(100),"
				+ HANDOUTSUBJSTRING + " vachar(200),"
				+ HANDOUTPICCNTSTRING + " int,"
				+ HANDOUTDLPICCNTSTRING + " int,"
				+ HANDOUTFESTRING + " vachar(10),"
				+ HANDOUTCLASSSTRING + " vachar(10),"
				+ HANDOUTQNSTRING + " integer,"
				+ HANDOUTLUTIMESTRING + " integer,"
				+ HANDOUTURLPATHSTRING +" varhcar(100)"
				+ ")"
				);
		//訊息
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLEMESSAGE
				+ "([_id] INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ MSGIDSTRING + " integer,"
				+ MSGRECEIVERIDSTRING + " varchar(32),"
				+ MSGCONTSTRING + " varchar(500),"
				+ MSGFLAGSTRING + " int"
				+ ")"
				);
		//問卷調查分類
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLESURVEYCLASS 
				+ "("
				+ SCIDSTRING + " integer," 
				+ SCNAMESTRING + " vachar(50)"											
				+ ")"
				);
		//問卷調查
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLESURVEY
				+"("
				+ SURVEYIDSTRING + " integer,"
				+ SURVEYSUBJSTRING + " vachar(200),"
				+ SURVEYBGTIMESTRING + " integer,"
				+ SURVEYEDTIMESTRING + " integer,"
				+ SURVEYREMARKSTRING + " vachar(200),"
				+ SURVEYCLASSSTRING + " vachar(10),"
				+ SURVEYQNSTRING +" integer,"
				+ SURVEYLUTIMESTRING+" integer"
				+")"
				);
		//問題
		db.execSQL("CREATE TABLE IF NOT EXISTS " +TABLEQUESTION
				+"("
				+ QUESTIONNAIREIDSTRING +" integer,"
				+ QUESTIONIDSTRING + " integer,"
				+ QUESTIONNOSTRING + " integer,"
				+ QUESTIONSUBJSTRING + " vachar(500),"
				+ ANSWERTYPESTRING + " vachar(20),"
				+ OPTIONCNTSTRING + " int,"
				+ MINSELECTCNTSTRING + " int,"
				+ MAXSELECTCNTSTRING + " int,"
				+ OPTIONSTRING + " vachar(500)"
				+")"
				);
		
		//公佈欄
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLENEWS
				+"("
				+ NEWSIDSTRING + " integer,"
				+ NEWSSUBJSTRING + " vachar(100),"
				+ PUBLISHERSTRING+" varchar(50),"
				+ NEWSCONTSTRING + " vachar(500),"
				+ NEWSBGTIMESTRING + " integer,"
				+ NEWSEDTIMESTRING + " integer"
				+")"
				);
		//userlog
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLEUSERLOG
				+"("+USERLOGIDSTRING+" integer primary key autoincrement,"
				+ USERLOGUSERIDSTRING + " vachar(30),"
				+ USERLOGCASECODESTRING + " vachar(50),"
				+ USERLOGCLASSSTRING + " vachar(50),"
				+ USERLOGTYPESTRING + " integer,"
				+ USERLOGPARENTIDSTRING + " integer,"
				+ USERLOGTIMESTRING + " integer"
				+")"
				);
		
		//問卷調查結果
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLESURVEYRESULT
				+"("+SRIDSTRING+" integer primary key autoincrement,"
				+ DOCTORNAMESTRING + " vachar(30),"
				+ HOSPITALSTRING + " vachar(50),"
				+ DIVISIONSSTRING + " vachar(50),"
				+ SRSURVEYIDSTRING + " integer,"
				+ SRQUESTIONNAIRESTRING + " integer,"
				+ SURVEYRESULTCONTSTRING + "  vachar(1000),"
				+ SURVEYRESULTTIMESTRING + "  integer,"
				+ SURVEYRESULTFLAGSTRING + "  integer"
				+")"
				);
		
		/*db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLEPUNCHCARDINFO
				+"("
				+PCRIDSTRING + " vachar(10),"
				+LOGINUSERIDSTRING + " vachar(30),"
				+HOSPTIALIDSTRING + " vachar(10),"
				+HOSPITALNAME + " vachar(50),"
				+DOCTORIDSTRING + " vachar(10),"
				+DOCTORNAMESTRING + " vachar(50),"
				+ACTIVITYSTRING + " vachar(100),"
				+DUTYLATSTRING + " float,"
				+DUTYLNGSTRING + " float"
				+")"
				);*/
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}

}
